package mwa.cs.dao;
import java.util.List;
import java.util.Map;

import mwa.cs.database.Database;
import mwa.cs.model.VatRate;

/**
 * A class used for managing vat rat
 */
public class VatRateDAO {
	private Database db;
    /**
     * Instantiate a new object of this class
     * @param database a database
     */
    public VatRateDAO(Database db) {
        this.db = db;
    }
	
	// ดึงข้อมูล อัตราภาษี 
		public Map<String, Object> getVatRate(int pnSiteId, String paCurrentDate) {
			String vaSql = "SELECT NVL(rate,1) AS rate,start_date,end_date  "
					+ "FROM vat_rate WHERE site_id = ? and start_date <= ? and end_date >= ?";
			Map<String, Object> result = db.querySingle(vaSql, pnSiteId, paCurrentDate, paCurrentDate);
			if (result != null) {
				return result;
			} else {
				return null;
			}
		}
		
	//select start_date,end_date	
		 public List<Map<String, Object>> findVatRateBySite(int pnSiteId) {
		        String vaSql = "SELECT NVL(rate,1) AS rate,start_date,end_date FROM vat_rate WHERE site_id = ? ORDER BY start_date ASC";
		        List<Map<String, Object>> result = db.queryList(vaSql, pnSiteId);
		        if (result != null) {
		            return result;
		        } else {
		            return null;
		        }
		    }
		
		 //get start_date ,end_date,rate
		 public String findRateBySiteAndStartDate(int pnSiteId,String paStarDate , String paField) {
		       
			 	String vaSql = "SELECT " + paField + " FROM vat_rate WHERE site_id = ? and start_date = ? ";
		        Map<String, Object> result = db.querySingle(vaSql, pnSiteId, paStarDate);
		        if (result != null) {
		        	return (String) result.get(paField).toString();
	        } else {
		            return null;
		        }
		    }
		
		 // get strat_date ,end_date for check before insert data
		 public List<Map<String, Object>> findCheckStartDateAndEndDateBySite(int pnSiteId,String paStartDate,String paEndDate) {
		        String vaSql = "SELECT start_date,end_date FROM vat_rate "
		        			+ "WHERE site_id = ? AND start_date<=? AND end_Date>=?  ";
		        List<Map<String, Object>> result = db.queryList(vaSql, pnSiteId,paStartDate,paEndDate);
		        if (result != null) {
		            return result;
		        } else {
		            return null;
		        }
		    }
		 
		// get strat_date ,end_date for check before update data
				 public List<Map<String, Object>> findsCheckStartDateAndEndDateBySite(int pnSiteId,String paOldStartDate,String paOldEndDate,String paNewStratDate, String paNewEndDate) {
				        String vaSql = "SELECT start_date,end_date FROM vat_rate "
				        			+ "WHERE site_id = ? AND start_date!=? AND end_date!=?"
				        			+ " AND start_date<=? AND end_date>=?  ";
				        List<Map<String, Object>> result = db.queryList(vaSql, pnSiteId,paOldStartDate,paOldEndDate,paNewStratDate,paNewEndDate);
				        if (result != null) {
				            return result;
				        } else {
				            return null;
				        }
				    }

		 
		 //insert vat_rate on database
		 public void addVatRate(VatRate poVatRate) {
	    	 String vaSql = "INSERT INTO VAT_RATE (SITE_ID,RATE,START_DATE,END_DATE,INSERT_USER_ID,INSERT_DATE,INSERT_TIME,"
	    	 								+ "UPDATE_USER_ID,UPDATE_DATE,UPDATE_TIME)"
	    			 						+"VALUES(?,?,?,?,?,?,?,?,?,?)";
	    			try {
	    					db.insert(vaSql, poVatRate.getVnSiteId(),
	    							poVatRate.getVnRate(),
	    							poVatRate.getVaStartDate(),
	    							poVatRate.getVaEndDate(),
	    							poVatRate.getVnInsertUserId(),
	    							poVatRate.getVaInsertDate(),
	    							poVatRate.getVaInsertTime(),
	    							poVatRate.getVnUpdateUserId(),
	    							poVatRate.getVaUpdateDate(),
	    							poVatRate.getVaUpdateTime());
	    				
	    			}catch (Exception e) {
	    	        	throw new RuntimeException(e);
	    	        }
	    	}
		 
		 //update vat_rate
		 public void updateVatRate(int pnSiteId,String paOldStartDate,String paOldEndDate,VatRate poVatRate) {
	    	 //String vaSql = "UPDATE ZONE SET ZONE_NAME = ? ,UPDATE_USER_ID=?,UPDATE_DATE=?,UPDATE_TIME=?"
	    	 	// 	+ "WHERE SITE_ID=? and ZONE_ID=?";
	    	 	 
	    	String vaSql  = " UPDATE VAT_RATE SET " +
	    				  " RATE= ? , " +
	    				  " START_DATE = ? , " +
	    				  " END_DATE  = ? , " +
	    				  " UPDATE_USER_ID = ? , "+
	    				  " UPDATE_DATE = ? , " +
	    				  " UPDATE_TIME = ? " +
	    				  " WHERE " + 
	    				  " START_DATE  = ? " +
	    				  " AND END_DATE  = ? " +
	    				  " AND SITE_ID = ? ";
	    	
	    	 try {
					db.update(vaSql, poVatRate.getVnRate() , 
							poVatRate.getVaStartDate(),
							poVatRate.getVaEndDate(),
							poVatRate.getVnUpdateUserId(),
							poVatRate.getVaUpdateDate(),
							poVatRate.getVaUpdateTime(),
							paOldStartDate,
							paOldEndDate,
							pnSiteId);
								
			}catch (Exception e) {
				throw new RuntimeException(e);
			}
	    }
		 //delete vat_rate
		 public boolean deleteVatRateBySiteIdAndStartDateAndEndDate(int pnSiteId,String paStartDate,String paEndDate) {
		        String vaSql = "DELETE FROM VAT_RATE WHERE SITE_ID = ? and START_DATE = ? and END_DATE=?";
		        try {
		            db.update(vaSql, pnSiteId, paStartDate,paEndDate);
		            return true;
		        } catch (Exception e) {
		            return false;
		        }
		    }
}
